Troubles that occurred during the project 3

Summary:

1. Loading the source data into SQL Server Database was hard since source data was large.

2.  Understanding Source data without Data Dictionary.

3. Referential integrity was missing between tables.

4.  Implementing Slowly Changing Dimension type 1 was challenging.

 .

Solutions for the troubles that occurred during different phases of the project: 

 

1. While loading source (OLTP) data into SQL Server Database it was hard, since data was large. We had to use Command Prompt to load the data into a SQL Server Database.  In the below image, you see how we used Command Prompt to load the data into SQL Server Database.

2.  There was no Data Dictionary provided to know about the definition of metadata.  We need to go through each column manually and look at each column names and values to understand the data.
To get more clarity let's have a look at some example in the below image :
1.  Is the name of the Database.
2. Is the name of the Table.
3. Showing what table is queried.
4. BDY is a column name it means Body of the car.
5. GLS is a column name it means Glass of the car.


 

3. The source data we got doesn't have Foreign Key relationship, in the Primary Table that has Primary key. It will help to maintain referential integrity . we had to manually check the matching values of related tables to create Foreign Key constraint. By having this relationship we can join multiple tables to create Data Warehouse which has multiple Dimensional and Fact Tables. For details see the below image.

1.  Key symbol facing down represents Primary Key.

2. In SQL Server if you expand your Estimates database you will see option Database Diagram right click and select New Database diagram. SQL Server will try to automatically identify relationship. For our database it created only one relationship. But for the rest of the tables it was not able to create relationship, therefore we created manually.


4.  As we need to unpivot 62 columns into one single column, while creating Dimensions and Fact tables by implementing Slowly Change Dimensions type 1 was challenging as execution time was more.

1. This is the Data Warehouse Database created using a Stored Procedure.
2. Five Dimensional tables and one Fact table created in Star Schema Model.
3.  This is the Fact table holding 62 columns data into one column.
4.  You can see all the columns details.
5. It has 5961672 rows.




Troubles that occurred during the project 3

Summary:

1. Loading the source data into SQL Server Database was hard since source data was large.

2.  Understanding Source data without Data Dictionary.

3. Referential integrity was missing between tables.

4.  Implementing Slowly Changing Dimension type 1 was challenging.

 .

Solutions for the troubles that occurred during different phases of the project: 

 

1. While loading source (OLTP) data into SQL Server Database it was hard, since data was large. We had to use Command Prompt to load the data into a SQL Server Database.  In the below image, you see how we used Command Prompt to load the data into SQL Server Database.

2.  There was no Data Dictionary provided to know about the definition of metadata.  We need to go through each column manually and look at each column names and values to understand the data.
To get more clarity let's have a look at some example in the below image :
1.  Is the name of the Database.
2. Is the name of the Table.
3. Showing what table is queried.
4. BDY is a column name it means Body of the car.
5. GLS is a column name it means Glass of the car.


 

3. The source data we got doesn't have Foreign Key relationship, in the Primary Table that has Primary key. It will help to maintain referential integrity . we had to manually check the matching values of related tables to create Foreign Key constraint. By having this relationship we can join multiple tables to create Data Warehouse which has multiple Dimensional and Fact Tables. For details see the below image.

1.  Key symbol facing down represents Primary Key.

2. In SQL Server if you expand your Estimates database you will see option Database Diagram right click and select New Database diagram. SQL Server will try to automatically identify relationship. For our database it created only one relationship. But for the rest of the tables it was not able to create relationship, therefore we created manually.


4.  As we need to unpivot 62 columns into one single column, while creating Dimensions and Fact tables by implementing Slowly Change Dimensions type 1 was challenging as execution time was more.

1. This is the Data Warehouse Database created using a Stored Procedure.
2. Five Dimensional tables and one Fact table created in Star Schema Model.
3.  This is the Fact table holding 62 columns data into one column.
4.  You can see all the columns details.
5. It has 5961672 rows.